The following creates two tables: TABLE1 (Point - Spatial) and TABLE2 (Non-Spatial). TABLE1 has an GID (Graphical ID) number, PROP1, PROP2 and a geometry column. TABLE2 has an AID (Attribute ID) number, the same PROP1 as found in TABLE1 and a unique PROP3. The tables are populated with three records. A multi-table view is created which will be recognized as a point feature when registered in the Geospatial Administrator. Finally triggers are set up to insert new point features into TABLE1, and to update and modify existing records in TABLE1 and TABLE2.

------------------------------------------------------------------------------------
-- Create TABLE1 table
------------------------------------------------------------------------------------
DROP TABLE TABLE1 CASCADE CONSTRAINT;


CREATE TABLE "TABLE1" 
(
  GID NUMBER CONSTRAINT TABLE1_PK PRIMARY KEY,
  PROP1 VARCHAR2(20),
  PROP2 VARCHAR2(20), 
  GEOMETRY MDSYS.SDO_GEOMETRY);


------------------------------------------------------------------------------------
-- Insert spatial metadata
------------------------------------------------------------------------------------
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='TABLE1';


INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
  VALUES ('TABLE1', 'Geometry',
    MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('X', 2197290.78, 2401264.08, 0.00000005),
    MDSYS.SDO_DIM_ELEMENT('Y', 703310.077, 911592.401, 0.00000005)
),
NULL);


------------------------------------------------------------------------------------
-- Create spatial index
------------------------------------------------------------------------------------
DROP INDEX TABLE1_SIDX;

CREATE INDEX TABLE1_SIDX ON TABLE1(Geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2
PARAMETERS ('layer_gtype=POINT, sdo_indx_dims=2')
;


------------------------------------------------------------------------------------
-- Insert three POINT records into TABLE1 
------------------------------------------------------------------------------------
Insert into TABLE1 (GID,PROP1,PROP2,GEOMETRY) values (1,'ABC','123',
MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(2300050, 814052, NULL), NULL, NULL));

Insert into TABLE1 (GID,PROP1,PROP2,GEOMETRY) values (2,'DEF','456',
MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(2300510, 814000, NULL), NULL, NULL));

Insert into TABLE1 (GID,PROP1,PROP2,GEOMETRY) values (3,'GHI','789',
MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(2300200, 813810, NULL), NULL, NULL));


------------------------------------------------------------------------------------
-- Create a sequence for new records.  Note that it starts at 4.
-- This is because three records were previously inserted.
------------------------------------------------------------------------------------
DROP SEQUENCE "TABLE1_SEQ";

CREATE SEQUENCE TABLE1_SEQ 
    START WITH 4 INCREMENT BY 1;


 
------------------------------------------------------------------------------------
-- Create TABLE2 table
------------------------------------------------------------------------------------
DROP TABLE TABLE2 CASCADE CONSTRAINT;

CREATE TABLE "TABLE2" 
   (
AID NUMBER PRIMARY KEY,
PROP1 VARCHAR2(20), 
PROP3 VARCHAR2(20)); 
  

------------------------------------------------------------------------------------
-- Insert three records into TABLE2 table.
-- Note that PROP1 in TABLE1 matches PROP1 in TABLE2. 
------------------------------------------------------------------------------------
INSERT INTO TABLE2 (AID, PROP1, PROP3) VALUES (1, 'ABC', '5500');
INSERT INTO TABLE2 (AID, PROP1, PROP3) VALUES (2, 'DEF', '5510');
INSERT INTO TABLE2 (AID, PROP1, PROP3) VALUES (3, 'GHI', '5520');


------------------------------------------------------------------------------------
-- Create a multi-table view, disable the primary key, and update the spatial metadata.
------------------------------------------------------------------------------------
DROP VIEW 2TABLE_VIEW;

CREATE OR REPLACE VIEW 2TABLE_VIEW AS 
SELECT TABLE1.GID, TABLE1.PROP1, TABLE1.PROP2, TABLE1.GEOMETRY, TABLE2.PROP3 
FROM TABLE1, TABLE2 
WHERE  TABLE1.GID = TABLE2.AID;

ALTER VIEW 2TABLE_VIEW ADD PRIMARY KEY (GID) DISABLE;


DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='2TABLE_VIEW';


INSERT INTO USER_SDO_GEOM_METADATA 
  SELECT '2TABLE_VIEW', COLUMN_NAME, DIMINFO, SRID 
  FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='TABLE1';
  

  
------------------------------------------------------------------------------------
-- Create INSERT, UPDATE and DELETE triggers.
------------------------------------------------------------------------------------
DROP TRIGGER 2TABLE_VIEW_INS;

CREATE OR REPLACE TRIGGER 2TABLE_VIEW_INS
INSTEAD OF INSERT ON 2TABLE_VIEW
BEGIN
  INSERT INTO TABLE1
   VALUES (:new.GID, :new.PROP1, :new.PROP2, :new.GEOMETRY);

  INSERT INTO TABLE2 VALUES (:new.GID, :new.PROP1, :new.PROP3);

END 2TABLE_VIEW_INS;
/


DROP TRIGGER 2TABLE_VIEW_UPD;

CREATE OR REPLACE TRIGGER 2TABLE_VIEW_UPD
INSTEAD OF UPDATE ON 2TABLE_VIEW
BEGIN
  UPDATE TABLE1 
  SET
   GID = :new.GID, PROP1 = :new.PROP1, PROP2 =  :new.PROP2, GEOMETRY = :new.GEOMETRY
  WHERE GID = :old.GID;

  UPDATE TABLE2 SET
  AID = :new.GID, PROP1 = :new.PROP1, PROP3 = :new.PROP3
  WHERE AID = :old.GID;

END 2TABLE_VIEW_UPD;

/


DROP TRIGGER 2TABLE_VIEW_DEL;

CREATE OR REPLACE TRIGGER 2TABLE_VIEW_DEL
   INSTEAD OF DELETE ON 2TABLE_VIEW
BEGIN
  DELETE FROM TABLE1 WHERE GID = :old.GID;
  DELETE FROM TABLE2 WHERE AID = :old.GID;
END 2TABLE_VIEW_DEL;
/

COMMIT;